package shellstation;

import java.sql.*;
import java.util.Date;
import java.text.DateFormat;
import java.sql.SQLException;


public class InventoryReportController {

    private Connection conn = null;
    private Statement stmt;

    public void ConnectToDatabase(){
        String url = "jdbc:mysql://localhost:3306/";
        String dbName = "shellstation";
        String driver = "com.mysql.jdbc.Driver";
        String userName = "root";
        String password = "p@ssword";

        try{

            Class.forName(driver).newInstance();
            conn = DriverManager.getConnection(url+dbName,userName,password);

        }

        catch (Exception e) {
            e.printStackTrace();
        }
    }

    public ResultSet selectProduct(){

        ResultSet rs;

        try{
            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT CONCAT(i.productname, ' ',i.size) AS 'product'  FROM inventory i GROUP BY i.productID;");

        }
        catch (Exception e){
            e.printStackTrace();
            return null;
        }

        return rs;
    }

    public ResultSet selectQStock(String date){

        ResultSet rs;


        try{
            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT s.productID AS 'ID', SUM(s.quantity) AS 'stock' FROM inventory i "+
                                    "LEFT OUTER JOIN stockdetails s ON i.productID = s.productID "
                                    + "WHERE stock_date < '"+ date + "' OR s.quantity IS NULL" +
                                    " GROUP BY i.productID;");
        }
        catch (Exception e){
            e.printStackTrace();
            return null;
        }

        return rs;
    }

    public ResultSet selectQReceipt(String date){

        ResultSet rs;


        try{
            ConnectToDatabase();
            Statement stmt = conn.createStatement();


            rs = stmt.executeQuery("SELECT rd.productID AS 'ID', SUM(rd.quantity) AS 'receipt' FROM inventory i "+
                                    "LEFT OUTER JOIN receiptdetails rd ON i.productID = rd.productID  "
                                    + "LEFT OUTER JOIN receipt r ON rd.receiptNumber = r.receiptNumber WHERE r.sale_date < '"+ date + "' OR rd.quantity IS NULL" +
                                    " GROUP BY i.productID;");
            
        }
        catch (Exception e){
            e.printStackTrace();
            return null;
        }

        return rs;
    }

    public ResultSet selectReceipts(String date){

        ResultSet rs;


        try{
            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT rd.productID AS 'ID', SUM(rd.quantity) AS 'receipt' FROM inventory i "+
                                    "LEFT OUTER JOIN receiptdetails rd ON i.productID = rd.productID   "
                                    + "LEFT OUTER JOIN receipt  r ON r.receiptNumber = rd.receiptNumber WHERE r.sale_date in ('"+ date + " ') OR rd.quantity IS NULL" +
                                    " GROUP BY i.productID;");
        }
        catch (Exception e){
            e.printStackTrace();
            return null;
        }

        return rs;
    }

    public ResultSet selectAdjustments(String date){

        ResultSet rs;


        try{
            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT s.productID AS 'ID', SUM(s.quantity) AS 'adjustments' FROM inventory i "+
                                    "LEFT OUTER JOIN stockdetails s ON i.productID = s.productID   "
                                    + "WHERE s.stock_date in ('"+ date + "') OR s.quantity IS NULL" +
                                    " GROUP BY i.productID;");
                    }
        catch (Exception e){
            e.printStackTrace();
            return null;
        }

        return rs;
    }

     public ResultSet selectPrice(String date){

        ResultSet rs;


        try{
            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT rd.productID AS 'ID', CONCAT('P',rd.price) AS 'price' FROM inventory i LEFT OUTER JOIN receiptdetails rd  "+
                                    "ON i.productID = rd.productID LEFT OUTER JOIN receipt r "
                                    + "ON r.receiptNumber = rd.receiptNumber WHERE r.sale_date in ('"+ date + "') OR rd.price IS NULL" +
                                    " GROUP BY i.productID;");
           
        }
        catch (Exception e){
            e.printStackTrace();
            return null;
        }

        return rs;
    }
     
     public ResultSet selectAmount(String date){

         ResultSet rs;
         try{
            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT rd.productID AS 'ID', CONCAT('P', SUM(rd.quantity) * rd.price) AS 'amount' FROM inventory i "+
                                    "LEFT OUTER JOIN receiptdetails rd ON i.productID = rd.productID  "
                                    + "LEFT OUTER JOIN receipt  r ON r.receiptNumber = rd.receiptNumber WHERE r.sale_date in ('"+ date + "') OR rd.quantity IS NULL" +
                                    " GROUP BY i.productID;");

        }
        catch (Exception e){
            e.printStackTrace();
            return null;
        }

        return rs;

    }
    
    public ResultSet selectMaxProduct(){
        ResultSet rs;
        
        try {
            ConnectToDatabase();
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT MAX(productID) as 'ID' from inventory");
            
        }
        catch (Exception e){
            e.printStackTrace();
            return null;
        }
     return rs;           
    }
}


    
